package com.spstudio.iforward.front.impl;

import java.security.Principal;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.ejb.EJBContext;
import javax.ejb.Schedule;
import javax.ejb.Singleton;
import javax.ejb.Startup;
import javax.inject.Inject;

import com.spstudio.iforward.core.bean.PageObject;
import com.spstudio.iforward.core.helper.DatabaseHelper;
import com.spstudio.iforward.core.helper.StoryQueryResult;
import com.spstudio.iforward.core.interfaces.IQueryResult;
import com.spstudio.iforward.end.impl.CandidateStorySessionBean;
import com.spstudio.iforward.end.interfaces.ISPCoreSystemBeanRemote;
import com.spstudio.iforward.front.Story;
import com.spstudio.iforward.front.interfaces.IStorySessionBeanRemote;
import com.spstudio.iforward.qualifier.SPCoreSystemSingleRemoteBean;

/**
 * Session Bean implementation class StorySessionBean
 */
@Singleton
@Startup
public class StorySessionBean implements IStorySessionBeanRemote {

	@Inject Principal principal;
	@Inject DatabaseHelper helper;
	@Inject @SPCoreSystemSingleRemoteBean ISPCoreSystemBeanRemote systemBean;
	@Resource EJBContext context;
	
	private static final int MAX_HOT_ITEM_COUNT = 6;
	
	private static final String QUERY_STORY = "SELECT s.id, sc.category_name, s.title, s.publish_date, s.comment, s.click_ranking, s.thumbnail_path FROM story s, story_category sc where s.story_category_id = sc.id and publish_date < sysdate() order by publish_date desc, id desc limit ?,?";
	private static final String QUERY_SINGLE_STORY = "SELECT s.id, sc.category_name, s.title, s.publish_date, s.comment, s.click_ranking, s.content FROM story s, story_category sc where s.story_category_id = sc.id and s.id = ? and publish_date < sysdate()";
	private static final String QUERY_SINGLE_STORY_PAGING = "(select id from story where id > ? limit 0 , 1) union (select id from story where id < ? order by id desc limit 0 , 1)";
	private static final String QUERY_LATEST_STORY = "SELECT s.id, sc.category_name, s.title, s.publish_date, s.comment, s.click_ranking, s.content FROM story s, story_category sc where s.story_category_id = sc.id and publish_date < sysdate() order by publish_date desc, id desc limit 0, 2";
	private static final String QUERY_CANDIDATE_STORY = "select story_category_id, title, content, comment, item_source from story_candidate where id = ?";
	private static final String QUERY_STORY_COUNT = "SELECT count(0) FROM story where publish_date < sysdate()";
	/*
	 * Query Hot Items
	 */
	private static final String QUERY_HOT_ITEMS = "SELECT id, left(comment, 30), thumbnail_path, publish_date FROM story where publish_date > DATE_SUB(CURDATE(),INTERVAL 14 DAY) order by click_ranking desc limit 0, " + MAX_HOT_ITEM_COUNT;
	private static final String UPDATE_CANDIDATE_STORY_STATUS = "update story_candidate set review_status_id = 3, story_id = ? where id = ?";
	private static final String UPDATE_STORY_CATEGORY = "update story_category set story_sum = story_sum + 1 where id = ?";
	private static final String INSERT_STORY = "insert into story(story_category_id, title, publish_date, content, comment, click_ranking, publish_user, thumbnail_path, item_source) values(?,?,?,?,?,?,?,(SELECT concat(pr.path1,'/',pr.path2,'/',pr.res_name) FROM pic_resource pr where pr.story_candidate_id = ? and pr.thumbnail_flag = 1 limit 0,1), ?)";
	private static final String DELETE_STORY = "delete from story where id = ?";
	
	private IQueryResult<Story> cachedHomePageStory;
	private Map<Integer, IQueryResult<Story>> cachedOtherPageStory;
	private Map<Integer, Story> cachedSingleStory;
	private List<Story> hotItmeStoryList;
	
    /**
     * Default constructor. 
     */
	public StorySessionBean() {
	}
    
	@PostConstruct
	public void postConstruct() {
		cachedOtherPageStory = new HashMap<Integer, IQueryResult<Story>>();
		cachedSingleStory = new HashMap<Integer, Story>();
		hotItmeStoryList = new ArrayList<Story>();
		timer();
	}
	
//	@Schedule(minute = "*/10", hour = "*", persistent = false)
//	public void flushReadCount() {
//		
//	}
	
	@Schedule(hour = "*/2", persistent = false)
	public void timer() {
		IQueryResult<Story> returnResult = new StoryQueryResult();
		PageObject pageObject = new PageObject();
		queryStory(returnResult, pageObject);
		cachedHomePageStory = returnResult;
		if (cachedOtherPageStory != null){
			cachedOtherPageStory.clear();
		}
		
		queryHotItems();
	}
	
	private void queryHotItems(){
		List<Object[]> result = helper.doQuery(QUERY_HOT_ITEMS, null);
		if (result != null && result.size() != 0){
			Story tempBean = new Story();
			hotItmeStoryList.clear();
			for(Object[] row: result){
				Story bean = tempBean.clone();
				bean.setId((Integer)row[0]);
				bean.setTitle((String)row[1]);
				bean.setThumbnailPath("http://" + systemBean.retrievePictureHttpHost() + "/" + (String)row[2]);
				bean.setPublishDate(((Timestamp)row[3]).getTime());
				hotItmeStoryList.add(bean);
			}
		}
	}

	@Override
	public void publishStory(int candidateStoryId, long publishDate) {
		List<Object[]> candidateStoryList = helper.doQuery(QUERY_CANDIDATE_STORY, new Object[]{candidateStoryId});
		if (candidateStoryList != null && candidateStoryList.size() != 0){
			Object[] candidateStory = candidateStoryList.get(0);
			Object[] storyParameter = new Object[] { 
					candidateStory[0],
					candidateStory[1], 
					new Date(publishDate),
					((String)candidateStory[2]).replace("<img ", "<img class=\"lazy\" src=\"loading.gif\" ").replace("src=\"http", "data-original=\"http"), 
					candidateStory[3],
					20 + (int) (Math.random() * 500), principal.getName(),
					candidateStoryId,
					candidateStory[4]};
			int returnKey = helper.doDMLOperationWithGeneratedKeyReturn(INSERT_STORY, storyParameter);
			if (returnKey == -1){
				context.setRollbackOnly();
				return;
			}
			int effectRowCount = helper.doDMLOperation(UPDATE_CANDIDATE_STORY_STATUS, new Object[]{returnKey, candidateStoryId});
			if (effectRowCount != 1){
				context.setRollbackOnly();
				return;
			}
			effectRowCount = helper.doDMLOperation(UPDATE_STORY_CATEGORY, new Object[]{candidateStory[0]});
			if (effectRowCount != 1){
				context.setRollbackOnly();
				return;
			}
		}
	}
	
	@Override
	public IQueryResult<Story> loadMultiStory(Story condition, PageObject pageObject) {
		
		// prefer read cache
		if (pageObject.getCurrentPageNumber() == 1){
			if (cachedHomePageStory != null && cachedHomePageStory.getResultData().size() != 0){
				return cachedHomePageStory;
			}
		}else{
			if (cachedOtherPageStory.containsKey(pageObject.getCurrentPageNumber())){
				return cachedOtherPageStory.get(pageObject.getCurrentPageNumber());
			}
		}
		
		IQueryResult<Story> returnResult = new StoryQueryResult();
		if (condition.getCategoryId() == 0){
			queryStory(returnResult, pageObject);
		}
		
		if (pageObject.getCurrentPageNumber() != 1){
			while (cachedOtherPageStory.size() > 50){
				cachedOtherPageStory.remove(cachedOtherPageStory.keySet().iterator().next());
			}
			cachedOtherPageStory.put(pageObject.getCurrentPageNumber(), returnResult);
		}
		
		return returnResult;
	}

	private void queryStory(IQueryResult<Story> returnResult, PageObject pageObject){
		// query total number
		List<Object[]> result = helper.doQuery(QUERY_STORY_COUNT, null);
		if (result != null && result.size() != 0){
			pageObject.setTotalRecordsNumber((Long)result.get(0)[0]);
		}
		((StoryQueryResult)returnResult).setPageObject(pageObject);
		
		// query paging data
		Object[] params = new Object[] {
				pageObject.getOffset(),
				pageObject.getMaxRecordsPerPage()
			};
		result = helper.doQuery(QUERY_STORY, params);
		List<Story> listStory = new ArrayList<Story>();
		if (result != null && result.size() != 0){
			Story tempBean = new Story();
			for(Object[] row: result){
				Story bean = tempBean.clone();
				bean.setId((Integer)row[0]);
				bean.setCategoryName((String)row[1]);
				bean.setTitle((String)row[2]);
				bean.setPublishDate(((Timestamp)row[3]).getTime());
				bean.setComment((String)row[4]);
				bean.setRanking((Integer)row[5]);
				bean.setThumbnailPath("http://" + systemBean.retrievePictureHttpHost() + "/" + (String)row[6]);
				listStory.add(bean);
			}
		}
		((StoryQueryResult)returnResult).setListStory(listStory);
	}

	@Override
	public Story loadSingleStory(int id) {
		if (cachedSingleStory.containsKey(id)){
			return cachedSingleStory.get(id);
		}
		Story bean = null;
		List<Object[]> result = helper.doQuery(QUERY_SINGLE_STORY, new Object[]{id});
		if (result != null && result.size() != 0){
			Object[] row = result.get(0);
			bean = new Story();
			bean.setId((Integer)row[0]);
			bean.setCategoryName((String)row[1]);
			bean.setTitle((String)row[2]);
			bean.setPublishDate(((Timestamp)row[3]).getTime());
			bean.setComment((String)row[4]);
			bean.setRanking((Integer)row[5] + 1);
			bean.setContent(((String)row[6]).replace(CandidateStorySessionBean.WEB_HOST_NAME, "http://" + systemBean.retrievePictureHttpHost()));
			
			bean.setPreStoryId((Integer)row[0]);
			bean.setNextStoryId((Integer)row[0]);
			List<Object[]> pagingResult = helper.doQuery(QUERY_SINGLE_STORY_PAGING, new Object[]{(Integer)row[0], (Integer)row[0]});
			if (pagingResult != null && pagingResult.size() != 0){
				for (Object[] data: pagingResult){
					if ((Integer)data[0] > (Integer)row[0]){
						bean.setNextStoryId((Integer)data[0]);
					}else if ((Integer)data[0] < (Integer)row[0]){
						bean.setPreStoryId((Integer)data[0]);
					}
				}
			}
		}
		
		while (cachedSingleStory.size() > 100){
			cachedSingleStory.remove(cachedSingleStory.keySet().iterator().next());
		}
		cachedSingleStory.put(id, bean);
		
		// log
		
		
		return bean;
	}

	@Override
	public Story loadLatestStory() {
		Story bean = null;
		List<Object[]> result = helper.doQuery(QUERY_LATEST_STORY, null);
		if (result != null && result.size() != 0){
			Object[] row = result.get(0);
			bean = new Story();
			bean.setId((Integer)row[0]);
			bean.setCategoryName((String)row[1]);
			bean.setTitle((String)row[2]);
			bean.setPublishDate(((Timestamp)row[3]).getTime());
			bean.setComment((String)row[4]);
			bean.setRanking((Integer)row[5] + 1);
			bean.setContent(((String)row[6]).replace(CandidateStorySessionBean.WEB_HOST_NAME, "http://" + systemBean.retrievePictureHttpHost()));
			
			bean.setPreStoryId((Integer)row[0]);
			bean.setNextStoryId((Integer)row[0]);
			if (result.size() > 1){
				bean.setNextStoryId((Integer)(result.get(1)[0]));
			}
		}
		return bean;
	}

	@Override
	public void deletePublishedStory(int id) {
		helper.doDMLOperation(DELETE_STORY, new Object[]{id});
		if (cachedSingleStory.containsKey(id)){
			cachedSingleStory.remove(id);
		}
		timer();
	}

	@Override
	public List<Story> loadHotItems() {
		return hotItmeStoryList;
	}
}
